<?php

namespace Wintel\RestBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;

class GrapicController extends Controller
{
    /**
     * @param Request $request
     * @return JsonResponse
     * 图形报表 坐席通话 数据列表
     */
    public function agentCallAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        $wherParasm = $msg = $this->getWhereParams($addInfo);
        if (isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $group = $wherParasm['group'];
        $sta_type = $wherParasm['sta_type'];
        $where.=$wherParasm['where'];
        $params = array_merge($params, $wherParasm['params']);
        $count = $conn->fetchAll(
            'SELECT count(*) '.
            'FROM win_agcdr '.
            'WHERE '.$where.' AND result = 0 '.
            'GROUP BY '.$group,
            $params
        );
        $count = count($count);
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'win_agcdr', $addInfo);
        //200 ok
        //全部通话
        $list = $conn->fetchAll(
            'SELECT COUNT(id) AS total_num,ag_num,ag_name,ag_id,SUM(conn_secs) AS total_secs,'.
            'AVG(conn_secs) AS avg_secs,start_date '.
            'FROM  win_agcdr '.
            'WHERE '.$where.' AND result = 0 '.
            'GROUP BY '.$group.' ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $list_out = null;
        $list_in = null;
        $in_num = array();
        $out_num = array();
        if ($sta_type == 1) {
            $list_in = $conn->fetchAll(
                'SELECT COUNT(*) AS total_num, start_date '.
                'FROM win_agcdr '.
                'WHERE '.$where.' AND (call_type=2 OR call_type=4 OR call_type=6) AND result=0 '.
                'GROUP BY '.$group,
                $params
            );
            foreach ($list_in as  $value) {
                $in_num[$value['start_date']] = $value['total_num'];
            }
            $list_out = $conn->fetchAll(
                'SELECT COUNT(*) AS total_num, start_date '.
                'FROM win_agcdr '.
                'WHERE '.$where.' AND (call_type=1 OR call_type=3 OR call_type=5) AND result=0 '.
                'GROUP BY '.$group,
                $params
            );
            foreach ($list_out as  $value) {
                $out_num[$value['start_date']] = $value['total_num'];
            }
        } else {
            $list_in = $conn->fetchAll(
                'SELECT COUNT(*) AS total_num, ag_id '.
                'FROM win_agcdr '.
                'WHERE '.$where.' AND (call_type=2 OR call_type=4 OR call_type=6) AND result=0 '.
                'GROUP BY '.$group,
                $params
            );
            foreach ($list_in as  $value) {
                $in_num[$value['ag_id']] = $value['total_num'];
            }
            $list_out = $conn->fetchAll(
                'SELECT COUNT(*) AS total_num, ag_id '.
                'FROM win_agcdr '.
                'WHERE '.$where.' AND (call_type=1 OR call_type=3 OR call_type=5) AND result=0 '.
                'GROUP BY '.$group,
                $params
            );
            foreach ($list_out as  $value) {
                $out_num[$value['ag_id']] = $value['total_num'];
            }
        }
        $res = $temp = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                if ($sta_type == 1) {
                    //按日期
                    $temp['start_date'] = $v['start_date'];
                    if (isset($in_num[$v['start_date']])) {
                        $temp['in_num'] = $in_num[$v['start_date']];
                    } else {
                        $temp['in_num'] = 0;
                    }
                    if (isset($out_num[$v['start_date']])) {
                        $temp['out_num'] = $out_num[$v['start_date']];
                    } else {
                        $temp['out_num'] = 0;
                    }
                } else {
                    if (isset($in_num[$v['ag_id']])) {
                        $temp['in_num'] = $in_num[$v['ag_id']];
                    } else {
                        $temp['in_num'] = 0;
                    }
                    if (isset($out_num[$v['ag_id']])) {
                        $temp['out_num'] = $out_num[$v['ag_id']];
                    } else {
                        $temp['out_num'] = 0;
                    }
                    $temp['ag_num'] = $v['ag_num'];
                    $temp['ag_name'] = $v['ag_name'];
                }
                $temp['total_num'] = $v['total_num'];
                $temp['total_secs'] = $v['total_secs'];
                $temp['avg_secs'] = $v['avg_secs'];
                $res[] = $temp;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$res);
        return new JsonResponse($ret);
    }

    /**
     * @param Request $request
     * @return JsonResponse
     * 坐席工作量图表
     */
    public function agentWorkAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        $wherParasm = $msg = $this->getWhereParams($addInfo);
        if (isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $group = $wherParasm['group'];
        $sta_type = $wherParasm['sta_type'];
        $where.=$wherParasm['where'];
        $params = array_merge($params, $wherParasm['params']);

        $count = $conn->fetchAll(
            'SELECT count(*) '.
            'FROM rep_agent_hour '.
            'WHERE '.$where.
            'GROUP BY '.$group,
            $params
        );
        $count = count($count);
        $page = $this->get("validator.extend.custom")->getPageInfo($count, 'rep_agent_hour', $addInfo);
        //200 ok
        $list = $conn->fetchAll(
            'SELECT SUM(login_secs) AS total_login,SUM(conn_secs) AS total_conn,SUM(busy_secs) AS total_busy,'.
            'SUM(ready_secs) AS total_ready,SUM(wait_secs) AS total_wait,'.
            'SUM(in_num) AS total_in,SUM(out_num) AS total_out,ag_name,ag_num,start_date '.
            'FROM  rep_agent_hour '.
            'WHERE '.$where.
            'GROUP BY '.$group.' ORDER BY '.$page['sort'].' '.$page['order'].' LIMIT '.$page['start'].','.$page['limit'],
            $params
        );
        $res = $temp = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                if ($sta_type == 1) {
                    //按日期
                    $temp['start_date'] = $v['start_date'];
                } else {
                    $temp['ag_num'] = $v['ag_num'];
                    $temp['ag_name'] = $v['ag_name'];
                }
                $temp['total_login'] = $v['total_login'];
                $temp['total_conn'] = $v['total_conn'];
                $temp['total_busy'] = $v['total_busy'];
                $temp['total_ready'] = $v['total_ready'];
                $temp['total_wait'] = $v['total_wait'];
                $temp['total_in'] = $v['total_in'];
                $temp['total_out'] = $v['total_out'];
                $res[] = $temp;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>$count, 'data'=>$res);
        return new JsonResponse($ret);
    }

    /**
     * @param $addInfo
     * @return array|string
     * 验证各个参数；
     */
    private function getWhereParams($addInfo)
    {
        $where ='';
        if (isset($addInfo['filter'])) {
            if (!isset($addInfo['filter']['sta_type'])) {
                return array('code'=>'404','message'=>'filter中缺少参数sta_type');
            } else {
                $group = $addInfo['filter']['sta_type'] == 1 ? 'start_date' : 'ag_id';
                $sta_type = $addInfo['filter']['sta_type'] == 1 ? 1 : 2;
            }
            if (!isset($addInfo['filter']['ag_id'])) {
                return array('code'=>'405','message'=>'filter中缺少参数ag_id');
            } else {
                $ag_id = $addInfo['filter']['ag_id'];
                if (!is_array($ag_id) || empty($ag_id)) {
                    return array('code'=>'410','message'=>'ag_id格式不正确');
                }
                $ag_row = array();
                foreach ($ag_id as $v) {
                    $ag_row[] = (int)$v;
                }
                $ag_row = array_unique($ag_row);
                $str = implode(',', $ag_row);
                $where.=' AND ag_id IN ('.$str.')';
            }
            if (!isset($addInfo['filter']['time_stamp'])) {
                return array('code'=>'406','message'=>'filter中缺少参数time_stamp');
            } else {
                $time_stamp = $addInfo['filter']['time_stamp']; //用于验证时间的区别
                switch ($time_stamp) {
                    case 1:
                        //日,验证 2014-05-05
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->isDate('日期格式不正确', $date, 407);
                        if (!empty($msg) && is_array($msg)) {
                            return $msg;
                        }
                        $where.=" AND start_date = :start_date ";
                        $params['start_date'] = $date;
                        break;
                    case 2:
                        //月
                        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->regexRormat($reg, $date, '日期格式不正确', 407);
                        if (!empty($msg) && is_array($msg)) {
                            return $msg;
                        }
                        $date = explode('-', $date);
                        $where.=" AND start_year = :start_year AND start_month = :start_month ";
                        $params['start_year'] = $date[0];
                        $params['start_month'] = $date[1];
                        break;
                    case 3:
                        //自定义
                        $start_date = isset($addInfo['filter']['start_date']) ? $addInfo['filter']['start_date'] : 'cc' ;
                        $end_date = isset($addInfo['filter']['end_date']) ? $addInfo['filter']['end_date'] : 'cc' ;
                        $start_msg = $this->get('validator.extend.custom')->isDate('开始日期格式不正确', $start_date, 408);
                        $end_msg = $this->get('validator.extend.custom')->isDate('结束日期格式不正确', $end_date, 409);
                        if ((!empty($start_msg) && is_array($start_msg)) || (!empty($end_msg) && is_array($end_msg))) {
                            return $start_msg ? $start_msg : $end_msg;
                        }
                        $where.=" AND start_date >= :start_date AND start_date <= :end_date ";
                        $params['start_date'] = $start_date;
                        $params['end_date'] = $end_date;
                        break;
                    default:
                        return array('code'=>'411','message'=>'time_stamp值不正确');
                        break;
                }
            }
            return array('where'=>$where, 'params'=>$params, 'group'=>$group, 'sta_type'=>$sta_type);
        } else {
            //至少要传入 sta_type : 1 统计类型；
            return array('code'=>'412','message'=>'json中缺少filter');
        }
    }

    /**
     * 图形报表 满意度评价 数据列表
     * @param Request $request
     * @return JsonResponse
     */
    public function evaluateAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }
        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        if (isset($addInfo['filter'])) {
            if (!isset($addInfo['filter']['sta_type'])) {
                return new JsonResponse(array('code'=>'404','message'=>'filter中缺少参数sta_type'));
            } else {
                $group = $addInfo['filter']['sta_type'] == 1 ? 'start_date' : 'ag_id';
                $sta_type = $addInfo['filter']['sta_type'] == 1 ? 1 : 2;
            }
            if (!isset($addInfo['filter']['ag_id'])) {
                return new JsonResponse(array('code'=>'405','message'=>'filter中缺少参数ag_id'));
            } else {
                $ag_id = $addInfo['filter']['ag_id'];
                if (!is_array($ag_id) || empty($ag_id)) {
                    return new JsonResponse(array('code'=>'410','message'=>'ag_id格式不正确'));
                }
                $ag_row = array();
                foreach ($ag_id as $v) {
                    $ag_row[] = (int)$v;
                }
                $ag_row = array_unique($ag_row);
                $str = implode(',', $ag_row);
                $where.=' AND ag_id IN ('.$str.')';
            }
            if (!isset($addInfo['filter']['time_stamp'])) {
                return new JsonResponse(array('code'=>'406','message'=>'filter中缺少参数time_stamp'));
            } else {
                $time_stamp = $addInfo['filter']['time_stamp']; //用于验证时间的区别
                switch ($time_stamp) {
                    case 1:
                        //日,验证 2014-05-05
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->isDate('日期格式不正确', $date, 407);
                        if (!empty($msg) && is_array($msg)) {
                            return new JsonResponse($msg);
                        }
                        $where.=" AND start_date = :start_date ";
                        $params['start_date'] = $date;
                        break;
                    case 2:
                        //月
                        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->regexRormat($reg, $date, '日期格式不正确', 407);
                        if (!empty($msg) && is_array($msg)) {
                            return new JsonResponse($msg);
                        }
                        $date = explode('-', $date);
                        $where.=" AND start_year = :start_year AND start_month = :start_month ";
                        $params['start_year'] = $date[0];
                        $params['start_month'] = $date[1];
                        break;
                    case 3:
                        //自定义
                        $start_date = isset($addInfo['filter']['start_date']) ? $addInfo['filter']['start_date'] : 'cc' ;
                        $end_date = isset($addInfo['filter']['end_date']) ? $addInfo['filter']['end_date'] : 'cc' ;
                        $start_msg = $this->get('validator.extend.custom')->isDate('开始日期格式不正确', $start_date, 408);
                        $end_msg = $this->get('validator.extend.custom')->isDate('结束日期格式不正确', $end_date, 409);
                        if ((!empty($start_msg) && is_array($start_msg)) || (!empty($end_msg) && is_array($end_msg))) {
                            return new JsonResponse($start_msg ? $start_msg : $end_msg);
                        }
                        $where.=" AND start_date >= :start_date AND start_date <= :end_date ";
                        $params['start_date'] = $start_date;
                        $params['end_date'] = $end_date;
                        break;
                    default:
                        return new JsonResponse(array('code'=>'411','message'=>'time_stamp值不正确'));
                        break;
                }
            }
        } else {
            //至少要传入 sta_type : 1 统计类型；
            return new JsonResponse(array('code'=>'412','message'=>'json中缺少filter'));
        }
        //200 ok
        $list = array();
        if ($sta_type == 1) {
            $list = $conn->fetchAll(
                'SELECT COUNT(id) AS total_num, start_date, start_month, start_day, evaluate '.
                'FROM  win_agcdr '.
                'WHERE '.$where.' AND evaluate > 0 '.
                'GROUP BY evaluate,'.$group,
                $params
            );

        } else {
            $list = $conn->fetchAll(
                'SELECT COUNT(id) AS total_num, start_date, start_month, start_day, evaluate, ag_id, ag_num, ag_name '.
                'FROM  win_agcdr '.
                'WHERE '.$where.' AND evaluate > 0 '.
                'GROUP BY evaluate,'.$group,
                $params
            );
        }
        $res = array();
        if (!empty($list)) {
            $temp = array();
            foreach ($list as $v) {
                if ($sta_type == 2) {
                    //按日期
                    $temp['ag_id'] = $v['ag_id'];
                    $temp['ag_num'] = $v['ag_num'];
                    $temp['ag_name'] = $v['ag_name'];
                }
                $temp['total_num'] = $v['total_num'];
                $temp['start_date'] = $v['start_date'];
                $temp['start_month'] = $v['start_month'];
                $temp['start_day'] = $v['start_day'];
                $temp['evaluate'] = $v['evaluate'];
                $res[] = $temp;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>count($res), 'data'=>$res);
        return new JsonResponse($ret);
    }

    /**
     * 图形报表 技能组通话量 数据列表
     * @param Request $request
     * @return JsonResponse
     */
    public function queueCallAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");
        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {
            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {
            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        if (isset($addInfo['filter'])) {
            if (!isset($addInfo['filter']['sta_type'])) {
                return new JsonResponse(array('code'=>'404','message'=>'filter中缺少参数sta_type'));
            } else {
                $group = $addInfo['filter']['sta_type'] == 1 ? 'start_date' : 'queue_id';
                $sta_type = $addInfo['filter']['sta_type'] == 1 ? 1 : 2;
            }
            if (!isset($addInfo['filter']['queue_id'])) {
                return new JsonResponse(array('code'=>'405','message'=>'filter中缺少参数queue_id'));
            } else {
                $ag_id = $addInfo['filter']['queue_id'];
                if (!is_array($ag_id) || empty($ag_id)) {
                    return new JsonResponse(array('code'=>'410','message'=>'queue_id格式不正确'));
                }
                $ag_row = array();
                foreach ($ag_id as $v) {
                    $ag_row[] = (int)$v;
                }
                $ag_row = array_unique($ag_row);
                $str = implode(',', $ag_row);
                $where.=' AND queue_id IN ('.$str.')';
            }
            if (!isset($addInfo['filter']['time_stamp'])) {
                return new JsonResponse(array('code'=>'406','message'=>'filter中缺少参数time_stamp'));
            } else {
                $time_stamp = $addInfo['filter']['time_stamp']; //用于验证时间的区别
                switch ($time_stamp) {
                    case 1:
                        //日,验证 2014-05-05
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->isDate('日期格式不正确', $date, 407);
                        if (!empty($msg) && is_array($msg)) {
                            return new JsonResponse($msg);
                        }
                        $where.=" AND start_date = :start_date ";
                        $params['start_date'] = $date;
                        break;
                    case 2:
                        //月
                        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->regexRormat($reg, $date, '日期格式不正确', 407);
                        if (!empty($msg) && is_array($msg)) {
                            return new JsonResponse($msg);
                        }
                        $date = explode('-', $date);
                        $where.=" AND start_year = :start_year AND start_month = :start_month ";
                        $params['start_year'] = $date[0];
                        $params['start_month'] = $date[1];
                        break;
                    case 3:
                        //自定义
                        $start_date = isset($addInfo['filter']['start_date']) ? $addInfo['filter']['start_date'] : 'cc' ;
                        $end_date = isset($addInfo['filter']['end_date']) ? $addInfo['filter']['end_date'] : 'cc' ;
                        $start_msg = $this->get('validator.extend.custom')->isDate('开始日期格式不正确', $start_date, 408);
                        $end_msg = $this->get('validator.extend.custom')->isDate('结束日期格式不正确', $end_date, 409);
                        if ((!empty($start_msg) && is_array($start_msg)) || (!empty($end_msg) && is_array($end_msg))) {
                            return new JsonResponse($start_msg ? $start_msg : $end_msg);
                        }
                        $where.=" AND start_date >= :start_date AND start_date <= :end_date ";
                        $params['start_date'] = $start_date;
                        $params['end_date'] = $end_date;
                        break;
                    default:
                        return new JsonResponse(array('code'=>'411','message'=>'time_stamp值不正确'));
                        break;
                }
            }
        } else {
            //至少要传入 sta_type : 1 统计类型；
            return new JsonResponse(array('code'=>'412','message'=>'json中缺少filter'));
        }
        //200 ok
        if ($sta_type == 1) {
            $list = $conn->fetchAll(
                'SELECT SUM(in_num) AS total_innum, SUM(lost_num) AS total_lost, '.
                'SUM(conn_num) AS total_conn, SUM(conn15_num) AS total_15conn, '.
                'SUM(queue_secs) AS total_quesecs, SUM(conn_secs) AS total_connsecs, '.
                'SUM(wait_secs) AS total_waitsecs,SUM(deal_secs) AS total_dealsecs, start_date '.
                'FROM  rep_queue_hour '.
                'WHERE '.$where.
                'GROUP BY '.$group,
                $params
            );
        } else {
            $list = $conn->fetchAll(
                'SELECT SUM(in_num) AS total_innum, SUM(lost_num) AS total_lost, '.
                'SUM(conn_num) AS total_conn, SUM(conn15_num) AS total_15conn, '.
                'SUM(queue_secs) AS total_quesecs, SUM(conn_secs) AS total_connsecs, '.
                'SUM(wait_secs) AS total_waitsecs, SUM(deal_secs) AS total_dealsecs, queue_id, queue_name '.
                'FROM  rep_queue_hour '.
                'WHERE '.$where.
                'GROUP BY '.$group,
                $params
            );
        }
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $temp = array();
                if ($sta_type == 1) {
                    //按日期
                    $temp['start_date'] = $v['start_date'];
                } else {
                    $temp['queue_id'] = $v['queue_id'];
                    $temp['queue_name'] = $v['queue_name'];
                }
                $temp['total_innum'] = $v['total_innum'];
                $temp['total_lost'] = $v['total_lost'];
                $temp['total_conn'] = $v['total_conn'];
                $temp['total_15conn'] = $v['total_15conn'];
                $temp['total_quesecs'] = $v['total_quesecs'];
                $temp['total_connsecs'] = $v['total_connsecs'];
                $temp['total_waitsecs'] = $v['total_waitsecs'];
                $temp['total_dealsecs'] = $v['total_dealsecs'];
                $res[] = $temp;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'total'=>count($res), 'data'=>$res);
        return new JsonResponse($ret);
    }

    /**
     * 图形报表 手机固话分析 数据列表
     * @param Request $request
     * @return JsonResponse
     */
    public function callerTypeAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {

            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {

            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        if (isset($addInfo['filter'])) {
            if (!isset($addInfo['filter']['time_stamp'])) {

                return new JsonResponse(array('code'=>'404', 'message'=>'filter中缺少参数time_stamp'));
            } else {
                $time_stamp = $addInfo['filter']['time_stamp']; //用于验证时间的区别
                switch ($time_stamp) {
                    case 1:
                        //日,验证 2014-05-05
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->isDate('日期格式不正确', $date, 405);
                        if (!empty($msg) && is_array($msg)) {
                            return new JsonResponse($msg);
                        }
                        $where.=" AND start_date = :start_date ";
                        $params['start_date'] = $date;
                        break;
                    case 2:
                        //月
                        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->regexRormat($reg, $date, '日期格式不正确', 405);
                        if (!empty($msg) && is_array($msg)) {

                            return new JsonResponse($msg);
                        }
                        $date = explode('-', $date);
                        $where.=" AND start_year = :start_year AND start_month = :start_month ";
                        $params['start_year'] = $date[0];
                        $params['start_month'] = $date[1];
                        break;
                    case 3:
                        //自定义
                        $start_date = isset($addInfo['filter']['start_date']) ? $addInfo['filter']['start_date'] : 'cc' ;
                        $end_date = isset($addInfo['filter']['end_date']) ? $addInfo['filter']['end_date'] : 'cc' ;
                        $start_msg = $this->get('validator.extend.custom')->isDate('开始日期格式不正确', $start_date, 406);
                        $end_msg = $this->get('validator.extend.custom')->isDate('结束日期格式不正确', $end_date, 407);
                        if ((!empty($start_msg) && is_array($start_msg)) || (!empty($end_msg) && is_array($end_msg))) {

                            return new JsonResponse($start_msg ? $start_msg : $end_msg);
                        }
                        $where.=" AND start_date >= :start_date AND start_date <= :end_date ";
                        $params['start_date'] = $start_date;
                        $params['end_date'] = $end_date;
                        break;
                    default:

                        return new JsonResponse(array('code'=>'408', 'message'=>'time_stamp值不正确'));
                        break;
                }
            }
        } else {
            //至少要传入 sta_type : 1 统计类型；
            return new JsonResponse(array('code'=>'409', 'message'=>'json中缺少filter'));
        }
        //200 ok
        $list = $conn->fetchAll(
            'SELECT caller_type, SUM(nums) AS total '.
            'FROM  rep_caller_types '.
            'WHERE '.$where.
            'GROUP BY caller_type',
            $params
        );
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $temp = array();
                if ($v['caller_type'] == 0) {
                    $temp['mobile'] = $v['total'];
                } else {
                    $temp['telephone'] = $v['total'];
                }
                $res[] = $temp;
            }
        }

        $ret = array('code'=>200, 'message'=>'ok', 'total'=>count($res), 'data'=>$res);

        return new JsonResponse($ret);
    }

    /**
     * 图形报表  按小时进线分析 数据列表
     * @param Request $request
     * @return JsonResponse
     */
    public function incomingHourAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {

            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {

            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        if (isset($addInfo['filter'])) {
            if (!isset($addInfo['filter']['sta_type'])) {

                return new JsonResponse(array('code'=>'404', 'message'=>'filter中缺少参数sta_type'));
            } else {
                $group = $addInfo['filter']['sta_type'] == 1 ? 'start_date' : 'ag_id';
                $sta_type = $addInfo['filter']['sta_type'] == 1 ? 1 : 2;
            }
            if (!isset($addInfo['filter']['data'])) {

                return new JsonResponse(array('code'=>'405', 'message'=>'filter中缺少参数data'));
            } else {
                $data = $addInfo['filter']['data'];
                if (!is_array($data) || empty($data)) {

                    return new JsonResponse(array('code'=>'410', 'message'=>'data格式不正确'));
                }
                if ($sta_type == 2) {
                    $que_row = array();
                    foreach ($data as $v) {
                        $que_row[] = (int)$v;
                    }
                    $que_row = array_unique($que_row);
                    $str = implode(',', $que_row);
                    $where.=' AND que_id IN ('.$str.')';
                } else {
                    $tel_row = array();
                    foreach ($data as $v) {
                        $tel_row[] = $v;
                    }
                    $tel_row = array_unique($tel_row);
                    $str = implode(',', $tel_row);
                    $where .= ' AND server_num IN ('.$str.')';
                }
            }
            if (!isset($addInfo['filter']['time_stamp'])) {

                return new JsonResponse(array('code'=>'406', 'message'=>'filter中缺少参数time_stamp'));
            } else {
                $time_stamp = $addInfo['filter']['time_stamp']; //用于验证时间的区别
                switch ($time_stamp) {
                    case 1:
                        //日,验证 2014-05-05
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->isDate('日期格式不正确', $date, 407);
                        if (!empty($msg) && is_array($msg)) {

                            return new JsonResponse($msg);
                        }
                        $where.=" AND start_date = :start_date ";
                        $params['start_date'] = $date;
                        break;
                    case 2:
                        //月
                        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->regexRormat($reg, $date, '日期格式不正确', 407);
                        if (!empty($msg) && is_array($msg)) {

                            return new JsonResponse($msg);
                        }
                        $date = explode('-', $date);
                        $where.=" AND start_year = :start_year AND start_month = :start_month ";
                        $params['start_year'] = $date[0];
                        $params['start_month'] = $date[1];
                        break;
                    case 3:
                        //自定义
                        $start_date = isset($addInfo['filter']['start_date']) ? $addInfo['filter']['start_date'] : 'cc' ;
                        $end_date = isset($addInfo['filter']['end_date']) ? $addInfo['filter']['end_date'] : 'cc' ;
                        $start_msg = $this->get('validator.extend.custom')->isDate('开始日期格式不正确', $start_date, 408);
                        $end_msg = $this->get('validator.extend.custom')->isDate('结束日期格式不正确', $end_date, 409);
                        if ((!empty($start_msg) && is_array($start_msg)) || (!empty($end_msg) && is_array($end_msg))) {

                            return new JsonResponse($start_msg ? $start_msg : $end_msg);
                        }
                        $where.=" AND start_date >= :start_date AND start_date <= :end_date ";
                        $params['start_date'] = $start_date;
                        $params['end_date'] = $end_date;
                        break;
                    default:

                        return new JsonResponse(array('code'=>'411', 'message'=>'time_stamp值不正确'));
                        break;
                }
            }
        } else {
            //至少要传入 sta_type : 1 统计类型；
            return new JsonResponse(array('code'=>'412', 'message'=>'json中缺少filter'));
        }
        //200 ok
        if ($sta_type == 2) {
            $list = $conn->fetchAll(
                'SELECT que_id, que_name, SUM(nums) AS total, start_date, start_hour '.
                'FROM  rep_hour_innums '.
                'WHERE '.$where.
                'GROUP BY start_hour',
                $params
            );
        } else {
            $list = $conn->fetchAll(
                'SELECT server_num, SUM(nums) AS total, start_date, start_hour '.
                'FROM  rep_hour_innums '.
                'WHERE '.$where.
                'GROUP BY start_hour',
                $params
            );
        }
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $temp = array();
                $temp['start_hour'] = $v['start_hour'];
                $temp['total'] = $v['total'];
                $res[] = $temp;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'data'=>$res);

        return new JsonResponse($ret);
    }

    /**
     * 图形报表  按地区分析 数据列表
     * @param Request $request
     * @return JsonResponse
     */
    public function incomingAreaAction(Request $request)
    {
        $vcc_code = $request->get("vcc_code", 0);
        $info = $request->get('info', ''); //分页搜索相关信息；
        /** @var \Doctrine\DBAL\Connection $conn */
        $conn = $this->get("doctrine.dbal.default_connection");

        $msg = $vcc_id = $this->get('validator.custom')->checkVccCode($vcc_code);
        if (!empty($msg) && is_array($msg)) {

            return new JsonResponse($msg);
        }

        //403 json
        $msg = $addInfo = $this->get('validator.custom')->checkJson($info);
        if (!empty($msg) && isset($msg['code'])) {

            return new JsonResponse($msg);
        }
        $where = 'vcc_id = :vcc_id ';
        $params =  array('vcc_id' => $vcc_id);
        if (isset($addInfo['filter'])) {
            if (!isset($addInfo['filter']['sta_type'])) {

                return new JsonResponse(array('code'=>'404', 'message'=>'filter中缺少参数sta_type'));
            } else {
                $group = $addInfo['filter']['sta_type'] == 1 ? 'start_date' : 'ag_id';
                $sta_type = $addInfo['filter']['sta_type'] == 1 ? 1 : 2;
            }
            if (!isset($addInfo['filter']['data'])) {

                return new JsonResponse(array('code'=>'405', 'message'=>'filter中缺少参数data'));
            } else {
                $data = $addInfo['filter']['data'];
                if (!is_array($data) || empty($data)) {

                    return new JsonResponse(array('code'=>'410', 'message'=>'data格式不正确'));
                }
                if ($sta_type == 2) {
                    $que_row = array();
                    foreach ($data as $v) {
                        $que_row[] = (int)$v;
                    }
                    $que_row = array_unique($que_row);
                    $str = implode(',', $que_row);
                    $where.=' AND que_id IN ('.$str.')';
                } else {
                    $tel_row = array();
                    foreach ($data as $v) {
                        $tel_row[] = $v;
                    }
                    $tel_row = array_unique($tel_row);
                    $str = implode(',', $tel_row);
                    $where .= ' AND server_num IN ('.$str.')';
                }
            }
            if (!isset($addInfo['filter']['time_stamp'])) {

                return new JsonResponse(array('code'=>'406', 'message'=>'filter中缺少参数time_stamp'));
            } else {
                $time_stamp = $addInfo['filter']['time_stamp']; //用于验证时间的区别
                switch ($time_stamp) {
                    case 1:
                        //日,验证 2014-05-05
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->isDate('日期格式不正确', $date, 407);
                        if (!empty($msg) && is_array($msg)) {

                            return new JsonResponse($msg);
                        }
                        $where.=" AND start_date = :start_date ";
                        $params['start_date'] = $date;
                        break;
                    case 2:
                        //月
                        $reg = "/^[1-9]\d{3}-(0[1-9]|1[0-2])$/";
                        $date = isset($addInfo['filter']['date']) ? $addInfo['filter']['date'] : 'cc' ;
                        $msg = $this->get('validator.extend.custom')->regexRormat($reg, $date, '日期格式不正确', 407);
                        if (!empty($msg) && is_array($msg)) {

                            return new JsonResponse($msg);
                        }
                        $date = explode('-', $date);
                        $where.=" AND start_year = :start_year AND start_month = :start_month ";
                        $params['start_year'] = $date[0];
                        $params['start_month'] = $date[1];
                        break;
                    case 3:
                        //自定义
                        $start_date = isset($addInfo['filter']['start_date']) ? $addInfo['filter']['start_date'] : 'cc' ;
                        $end_date = isset($addInfo['filter']['end_date']) ? $addInfo['filter']['end_date'] : 'cc' ;
                        $start_msg = $this->get('validator.extend.custom')->isDate('开始日期格式不正确', $start_date, 408);
                        $end_msg = $this->get('validator.extend.custom')->isDate('结束日期格式不正确', $end_date, 409);
                        if ((!empty($start_msg) && is_array($start_msg)) || (!empty($end_msg) && is_array($end_msg))) {

                            return new JsonResponse($start_msg ? $start_msg : $end_msg);
                        }
                        $where.=" AND start_date >= :start_date AND start_date <= :end_date ";
                        $params['start_date'] = $start_date;
                        $params['end_date'] = $end_date;
                        break;
                    default:

                        return new JsonResponse(array('code'=>'411', 'message'=>'time_stamp值不正确'));
                        break;
                }
            }
        } else {
            //至少要传入 sta_type : 1 统计类型；
            return new JsonResponse(array('code'=>'412', 'message'=>'json中缺少filter'));
        }
        //200 ok
        if ($sta_type == 2) {
            $list = $conn->fetchAll(
                'SELECT province_name, SUM(nums) AS total, que_id, que_name '.
                'FROM  rep_incdr_area '.
                'WHERE '.$where.
                'GROUP BY province_name',
                $params
            );
        } else {
            $list = $conn->fetchAll(
                'SELECT province_name, SUM(nums) AS total, server_num '.
                'FROM  rep_incdr_area '.
                'WHERE '.$where.
                'GROUP BY province_name',
                $params
            );
        }
        $res = array();
        if (!empty($list)) {
            foreach ($list as $v) {
                $temp = array();
                $temp['province_name'] = $v['province_name'];
                $temp['total'] = $v['total'];
                $res[] = $temp;
            }
        }
        $ret = array('code'=>200, 'message'=>'ok', 'data'=>$res);

        return new JsonResponse($ret);
    }
}
